# -*- coding: utf-8 -*-
import json

from typing   import Literal, Optional
from datetime import datetime

from aiosqlite import Connection


WEATHER_CONDITION = Literal["CLEAR", "CLOUDY", "RAIN", "SHOW", "FOG", "STORM"]


def is_datetime(string: str):
    try:
        datetime.fromisoformat(string)
        return True
    except:
        return False


def to_database(value):
    if isinstance(value, datetime):
        value = value.isoformat()
    elif isinstance(value, (list, dict, tuple)):
        value = json.dumps(value, ensure_ascii = False)

    return value


def from_database(value):
    if isinstance(value, str):
        if is_datetime(value):
            value = datetime.fromisoformat(value)
        else:
            for part in ("()", "[]", "{}"):
                start, end = part

                if value.startswith(start) and value.endswith(end):
                    value = json.loads(value)
                    break

    return value


def Base(table: str, keys: tuple):
    class Pattern(dict):
        _table_name:   str
        _service_keys: tuple

        _database: Connection


        def __missing__(self, *_):
            return None


        def __getattr__(self, name: str):
            return self[name]

        def __setattr__(self, name: str, value: str | int) -> None:
            if name in self.keys():
                return super().__setitem__(name, value)
            return super().__setattr__(name, value)


        def __call__(self, *args) -> dict:
            return {key: value for key, value in self.items() if key in args}
        

        @classmethod
        async def exists(cls, database: Connection = None, **kwargs) -> bool:
            expressions = []

            for key, value in kwargs.items():
                expression = f"{key} = "

                if isinstance(value, str):
                    expression += f'"{value}"'
                else:
                    expression += f"{value}"

                expressions.append(expression)

            sql = f"SELECT * FROM {cls._table_name}"

            if expressions != []:
                sql += f" WHERE {' AND '.join(expressions)}"

            try:
                cursor = await database.execute(sql)
                result = await cursor.fetchone()
            except:
                return False

            return bool(result)


        @classmethod
        async def get_by(cls, database: Connection = None, **kwargs) -> Optional["Pattern"]:
            database    = database or cls._database
            expressions = []

            for key, value in kwargs.items():
                expression = f"{key} = "

                if isinstance(value, str):
                    expression += f'"{value}"'
                else:
                    expression += f"{value}"

                expressions.append(expression)

            if expressions != []:
                cursor = await database.execute(f"SELECT * FROM {cls._table_name} WHERE {' AND '.join(expressions)}")
            else:
                cursor = await database.execute(f"SELECT * FROM {cls._table_name}")
    
            result = await cursor.fetchone()

            if not result:
                return None

            instance = cls({key: from_database(result[index]) for index, key in enumerate(("id", *cls._service_keys))})
            instance._database = database

            if not instance:
                return None

            return instance

        @classmethod
        async def get_by_id(cls, id: int, database: Connection) -> Optional["Pattern"]:
            return await cls.get_by(database, id = id)


        @classmethod
        async def create(cls, database: Connection, *args, **kwargs) -> Optional["Pattern"]:
            keys = cls._service_keys

            if len(args) == 0:
                kwargs = {key: to_database(value) for key, value in kwargs.items() if key in keys}
                cursor = await database.execute(
                    f"INSERT INTO {cls._table_name} (id, {', '.join(keys)}) VALUES (NULL, {', '.join([f':{key}' for key in keys])})",
                    kwargs)
            else:
                args   = [arg for arg in args if arg in keys]
                cursor = await database.execute(
                    f"INSERT INTO {cls._table_name} ({', '.join(keys)}) VALUES ({', '.join(['?'] * len(keys))})", 
                    args)

            await database.commit()
            return await cls.get_by(database, id = cursor.lastrowid)


        async def delete(self):
            try:
                await self._database.execute(f'DELETE FROM {self._table_name} WHERE id = {self.id}')
                await self._database.commit()
            except:
                return False
            finally:
                return True

        async def update(self, **kwargs):
            if kwargs == {}:
                return False

            try:
                update = []

                for key, value in kwargs.items():
                    if key in self and value != self[key]:
                        self[key] = value

                        if isinstance(value, str):
                            update.append(f'{key} = "{to_database(value)}"')
                        else:
                            update.append(f'{key} = {value}')

                if update == []:
                    return True

                await self._database.execute(f'UPDATE {self._table_name} SET {", ".join(update)} WHERE id = {self.id}')
                await self._database.commit()
            except:
                return False
            finally:
                return True

    base = Pattern

    base._table_name   = table
    base._service_keys = keys

    return base